package com.digitalconsulting.datalayer;

import java.io.File;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;

public class ItemListDataSource
{
	// Database fields
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	private String[] allColumns = { "ItemListId", "Price", "Place", "Description", "ImagePath", "Date", "Longitude", "Latitude" };

	public enum Sort
	{
		NONE, PRICE, DATE, LOCATION, DISTANCE
	}
	
	public ItemListDataSource(Context context)
	{
		dbHelper = new MySQLiteHelper(context);
	}
	
	public void open() throws SQLException
	{
		database = dbHelper.getWritableDatabase();
	}

	public void close()
	{
		dbHelper.close();
	}
	
	public ArrayList<ItemList> getAll(Sort sort)
	{
		String sortColumn;
		switch (sort) 
		{
			case DATE:
			sortColumn = "ItemListId DESC";
			break;
		
		    case PRICE:
			sortColumn = "Price";
			break;
			
		    default:
			sortColumn = "ItemListId";
		}
		
		open();
		Cursor c = database.query("ItemList", allColumns, null, null, null, null, sortColumn);

		ArrayList<ItemList> result = new ArrayList<ItemList>();
		c.moveToFirst();
		
		while (!c.isAfterLast())
		{
			ItemList t = new ItemList();
		   	t.setItemListId(c.getInt(0));
		   	t.setPrice(c.getFloat(1));
		   	t.setPlace(c.getString(2));
		   	t.setDescription(c.getString(3));
		   	t.setImagePath(c.getString(4));
		   	t.setDate(c.getString(5));
		   	t.setLongitude(c.getString(6));
		   	t.setLatitude(c.getString(7));
		   	result.add(t);
		   	c.moveToNext();
		}
		c.close();
		close();
		
		return result;
	}
	
	public void delete(int id)
	{
		String selectedFilePath = getFileName(id);
		
		File sd = Environment.getExternalStorageDirectory();
		File dest = new File(sd, selectedFilePath);		
		
		dest.delete();
		
		open();
		database.delete("ItemList", "ItemListId="+id, null);
		close();
	}
	
	public void insert(float price, String place, String description, String imagePath, String date, String longitude, String latitude)
	{
		ContentValues dataToInsert = new ContentValues();
		dataToInsert.put("Price", price);
		dataToInsert.put("Place", place);
		dataToInsert.put("Description", description);
		dataToInsert.put("ImagePath", imagePath);
		dataToInsert.put("Date", date);
		dataToInsert.put("longitude", longitude);
		dataToInsert.put("latitude", latitude);
		
		open();
		database.insert("ItemList", null, dataToInsert);
		close();
	}
	
	public String getFileName(int id)
	{
		open();
			
		String sql = "SELECT ImagePath FROM ItemList WHERE ItemListId = " + id;
			
		Cursor c = database.rawQuery(sql, null);
		c.moveToFirst();
		
		String fileName;
		fileName = c.getString(0);
		close();
			
		return fileName;
	}
	
	public int getTotalRows()
	{
		open();
		
		String sql = "SELECT COUNT(*) FROM ItemList";
		
		Cursor c = database.rawQuery(sql, null);
		c.moveToFirst();
		
		return c.getInt(0);
	}
	
}

